#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
远程批量安装MySQL二进制版本脚本
支持从配置表读取安装信息
"""

import paramiko
import sys
import time
import argparse
import json
import csv
import threading
from concurrent.futures import ThreadPoolExecutor, as_completed
from getpass import getpass
from datetime import datetime
import logging

# 配置日志
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s',
    handlers=[
        logging.FileHandler('mysql_install.log'),
        logging.StreamHandler(sys.stdout)
    ]
)
logger = logging.getLogger(__name__)

class MySQLBinaryInstaller:
    def __init__(self, host_info, global_config):
        self.host = host_info['host']
        self.username = host_info.get('username', global_config.get('default_username', 'root'))
        self.password = host_info.get('password') or global_config.get('default_password')
        self.port = host_info.get('port', global_config.get('default_port', 22))
        self.mysql_version = host_info.get('mysql_version', global_config.get('default_mysql_version', '8.0.33'))
        self.root_password = host_info.get('root_password') or global_config.get('default_root_password')
        self.install_dir = host_info.get('install_dir', global_config.get('default_install_dir', '/usr/local/mysql'))
        self.data_dir = host_info.get('data_dir', global_config.get('default_data_dir', '/usr/local/mysql/data'))
        self.mysql_port = host_info.get('mysql_port', global_config.get('default_mysql_port', 3306))
        
        self.ssh = None
        self.sftp = None
        self.install_result = {
            'host': self.host,
            'status': 'pending',
            'message': '',
            'start_time': None,
            'end_time': None
        }
    
    def connect(self):
        """建立SSH和SFTP连接"""
        try:
            self.ssh = paramiko.SSHClient()
            self.ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
            self.ssh.connect(
                hostname=self.host,
                port=self.port,
                username=self.username,
                password=self.password,
                timeout=30
            )
            
            self.sftp = paramiko.SFTPClient.from_transport(self.ssh.get_transport())
            logger.info(f"✅ 成功连接到 {self.host}")
            return True
        except Exception as e:
            error_msg = f"❌ 连接到 {self.host} 失败: {str(e)}"
            logger.error(error_msg)
            self.install_result['status'] = 'failed'
            self.install_result['message'] = error_msg
            return False
    
    def execute_command(self, command, show_output=False, timeout=60):
        """执行远程命令"""
        try:
            stdin, stdout, stderr = self.ssh.exec_command(command, timeout=timeout)
            exit_status = stdout.channel.recv_exit_status()
            
            output = stdout.read().decode('utf-8')
            error = stderr.read().decode('utf-8')
            
            if show_output:
                if output:
                    logger.debug(f"   {self.host} 输出: {output.strip()}")
                if error:
                    logger.debug(f"   {self.host} 错误: {error.strip()}")
            
            return exit_status, output, error
        except Exception as e:
            error_msg = f"{self.host} 命令执行失败: {str(e)}"
            logger.error(error_msg)
            return 1, "", str(e)
    
    def detect_os_architecture(self):
        """检测操作系统和架构"""
        logger.info(f"🔍 {self.host} 检测操作系统和架构...")
        
        # 检测操作系统
        exit_status, output, error = self.execute_command("cat /etc/os-release | grep '^ID='", False)
        if 'ubuntu' in output.lower() or 'debian' in output.lower():
            os_type = 'debian'
        elif 'centos' in output.lower() or 'rhel' in output.lower() or 'redhat' in output.lower():
            os_type = 'redhat'
        else:
            os_type = 'unknown'
        
        # 检测架构
        exit_status, arch_output, _ = self.execute_command("uname -m", False)
        architecture = arch_output.strip()
        
        logger.info(f"   {self.host} 操作系统: {os_type}, 架构: {architecture}")
        
        return os_type, architecture
    
    def download_mysql_binary(self, os_type, architecture):
        """下载MySQL二进制包"""
        logger.info(f"📥 {self.host} 下载MySQL二进制包...")
        
        # 根据操作系统和架构确定下载URL
        if architecture == 'x86_64':
            mysql_package = f"mysql-{self.mysql_version}-linux-glibc2.12-x86_64.tar.xz"
        else:
            error_msg = f"❌ {self.host} 不支持的架构: {architecture}"
            logger.error(error_msg)
            return None
        
        download_url = f"https://cdn.mysql.com/Downloads/MySQL-8.0/{mysql_package}"
        
        commands = [
            f"mkdir -p /tmp",
            f"cd /tmp && wget -c {download_url} -O {mysql_package}",
            f"ls -la /tmp/{mysql_package}"
        ]
        
        for command in commands:
            exit_status, output, error = self.execute_command(command)
            if exit_status != 0:
                if "ls -la" in command:
                    error_msg = f"❌ {self.host} 下载失败"
                    logger.error(error_msg)
                    return None
                else:
                    continue
        
        return mysql_package
    
    def create_mysql_user_group(self):
        """创建MySQL用户和组"""
        logger.info(f"👥 {self.host} 创建MySQL用户和组...")
        
        commands = [
            "groupadd mysql 2>/dev/null || echo 'group exists'",
            "useradd -r -g mysql -s /bin/false mysql 2>/dev/null || echo 'user exists'",
            "id mysql || echo '用户创建完成'"
        ]
        
        for command in commands:
            self.execute_command(command, False)
    
    def extract_and_install(self, package_name):
        """解压并安装MySQL"""
        logger.info(f"🔧 {self.host} 解压并安装MySQL到 {self.install_dir}...")
        
        commands = [
            # 创建安装目录
            f"mkdir -p {self.install_dir}",
            
            # 解压文件
            f"cd /tmp && tar xvf {package_name} -C {self.install_dir} --strip-components=1",
            
            # 设置权限
            f"chown -R mysql:mysql {self.install_dir}",
            f"chmod -R 755 {self.install_dir}"
        ]
        
        for command in commands:
            exit_status, output, error = self.execute_command(command)
            if exit_status != 0:
                error_msg = f"❌ {self.host} 安装失败: {error}"
                logger.error(error_msg)
                return False
        
        return True
    
    def initialize_mysql(self):
        """初始化MySQL"""
        logger.info(f"⚙️  {self.host} 初始化MySQL...")
        
        # 创建数据目录
        self.execute_command(f"mkdir -p {self.data_dir}", False)
        self.execute_command(f"chown -R mysql:mysql {self.data_dir}", False)
        
        # 初始化MySQL（不创建root密码）
        init_command = f"sudo -u mysql {self.install_dir}/bin/mysqld --initialize --user=mysql --datadir={self.data_dir}"
        exit_status, output, error = self.execute_command(init_command)
        
        if exit_status != 0:
            error_msg = f"❌ {self.host} 初始化失败: {error}"
            logger.error(error_msg)
            return False, ""
        
        # 提取临时密码
        temp_password = ""
        if "temporary password" in output:
            lines = output.split('\n')
            for line in lines:
                if "temporary password" in line:
                    temp_password = line.split()[-1]
                    break
        
        return True, temp_password
    
    def create_configuration(self):
        """创建MySQL配置文件"""
        logger.info(f"📝 {self.host} 创建MySQL配置文件...")
        
        config_content = f"""[mysqld]
# 基本设置
port = {self.mysql_port}
socket = /tmp/mysql.sock
datadir = {self.data_dir}
default_authentication_plugin = mysql_native_password

# 字符集设置
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# 连接设置
max_connections = 200
max_connect_errors = 10

# 缓存设置
key_buffer_size = 16M
max_allowed_packet = 16M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

# 日志设置
log-error = {self.data_dir}/mysqld.log
slow_query_log = 1
long_query_time = 2
slow_query_log_file = {self.data_dir}/slow.log

# InnoDB设置
innodb_data_home_dir = {self.data_dir}
innodb_buffer_pool_size = 128M
innodb_log_file_size = 48M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

[mysql]
default-character-set = utf8mb4

[client]
port = {self.mysql_port}
socket = /tmp/mysql.sock
default-character-set = utf8mb4
"""
        
        # 写入配置文件
        config_commands = [
            f"echo '{config_content}' > /tmp/my.cnf",
            f"mv /tmp/my.cnf /etc/my.cnf",
            "chown mysql:mysql /etc/my.cnf",
            "chmod 644 /etc/my.cnf"
        ]
        
        for command in config_commands:
            exit_status, output, error = self.execute_command(command)
            if exit_status != 0:
                error_msg = f"❌ {self.host} 配置文件创建失败: {error}"
                logger.error(error_msg)
                return False
        
        return True
    
    def create_systemd_service(self):
        """创建systemd服务文件"""
        logger.info(f"サービ {self.host} 创建systemd服务...")
        
        service_content = f"""[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target

[Service]
User=mysql
Group=mysql
ExecStart={self.install_dir}/bin/mysqld --defaults-file=/etc/my.cnf
TimeoutSec=0
RestartSec=2
Restart=always

[Install]
WantedBy=multi-user.target
"""
        
        commands = [
            f"echo '{service_content}' > /tmp/mysqld.service",
            "mv /tmp/mysqld.service /etc/systemd/system/mysqld.service",
            "systemctl daemon-reload",
            "systemctl enable mysqld.service"
        ]
        
        for command in commands:
            exit_status, output, error = self.execute_command(command)
            if exit_status != 0:
                error_msg = f"❌ {self.host} 服务文件创建失败: {error}"
                logger.error(error_msg)
                return False
        
        return True
    
    def start_mysql_service(self):
        """启动MySQL服务"""
        logger.info(f"🚀 {self.host} 启动MySQL服务...")
        
        commands = [
            "systemctl daemon-reload",
            "systemctl start mysqld",
            "systemctl status mysqld"
        ]
        
        for command in commands:
            exit_status, output, error = self.execute_command(command)
            if exit_status != 0 and "status" not in command:
                error_msg = f"❌ {self.host} 服务启动失败: {error}"
                logger.error(error_msg)
                return False
        
        return True
    
    def set_root_password(self, temp_password):
        """设置root密码"""
        logger.info(f"🔑 {self.host} 设置root密码...")
        
        if not temp_password:
            logger.warning(f"⚠️  {self.host} 无法获取临时密码")
            return False
        
        # 使用临时密码登录并修改密码
        change_password_sql = f"""
        ALTER USER 'root'@'localhost' IDENTIFIED BY '{self.root_password}';
        FLUSH PRIVILEGES;
        CREATE DATABASE IF NOT EXISTS test_db;
        """
        
        # 写入SQL到临时文件
        sql_file = "/tmp/mysql_init.sql"
        commands = [
            f"echo \"{change_password_sql}\" > {sql_file}",
            f"{self.install_dir}/bin/mysql -u root -p'{temp_password}' --connect-expired-password < {sql_file}",
            f"rm -f {sql_file}"
        ]
        
        for command in commands:
            exit_status, output, error = self.execute_command(command)
            if exit_status != 0:
                if "mysql -u root" not in command:  # 忽略mysql命令的返回值
                    continue
        
        return True
    
    def configure_firewall(self):
        """配置防火墙"""
        logger.info(f"🛡️  {self.host} 配置防火墙...")
        
        commands = [
            "firewall-cmd --permanent --add-port={self.mysql_port}/tcp 2>/dev/null || echo 'firewall-cmd not found'",
            "firewall-cmd --reload 2>/dev/null || echo 'firewall-cmd not found'",
            "ufw allow {self.mysql_port}/tcp 2>/dev/null || echo 'ufw not found'"
        ]
        
        for command in commands:
            self.execute_command(command, False)
    
    def verify_installation(self):
        """验证安装"""
        logger.info(f"✅ {self.host} 验证MySQL安装...")
        
        commands = [
            "systemctl status mysqld | grep 'active (running)'",
            f"{self.install_dir}/bin/mysql --version"
        ]
        
        for command in commands:
            exit_status, output, error = self.execute_command(command, False)
            if exit_status == 0:
                logger.info(f"✅ {self.host} MySQL正常运行")
                if "mysql --version" in command:
                    logger.info(f"   {self.host} 版本信息: {output.strip()}")
                return True
        
        return False
    
    def cleanup(self):
        """清理临时文件"""
        logger.info(f"🧹 {self.host} 清理临时文件...")
        self.execute_command("rm -f /tmp/mysql-*.tar.xz /tmp/*.sql", False)
    
    def install(self):
        """主要安装流程"""
        self.install_result['start_time'] = datetime.now()
        
        try:
            if not self.connect():
                return self.install_result
            
            # 检测操作系统和架构
            os_type, architecture = self.detect_os_architecture()
            
            if os_type == 'unknown':
                error_msg = f"❌ {self.host} 不支持的操作系统"
                logger.error(error_msg)
                self.install_result['status'] = 'failed'
                self.install_result['message'] = error_msg
                return self.install_result
            
            # 下载MySQL二进制包
            package_name = self.download_mysql_binary(os_type, architecture)
            if not package_name:
                self.install_result['status'] = 'failed'
                self.install_result['message'] = f"{self.host} 下载失败"
                return self.install_result
            
            # 创建用户组
            self.create_mysql_user_group()
            
            # 解压安装
            if not self.extract_and_install(package_name):
                self.install_result['status'] = 'failed'
                self.install_result['message'] = f"{self.host} 解压安装失败"
                return self.install_result
            
            # 创建配置文件
            if not self.create_configuration():
                self.install_result['status'] = 'failed'
                self.install_result['message'] = f"{self.host} 配置文件创建失败"
                return self.install_result
            
            # 初始化MySQL
            success, temp_password = self.initialize_mysql()
            if not success:
                self.install_result['status'] = 'failed'
                self.install_result['message'] = f"{self.host} MySQL初始化失败"
                return self.install_result
            
            # 创建systemd服务
            if not self.create_systemd_service():
                self.install_result['status'] = 'failed'
                self.install_result['message'] = f"{self.host} systemd服务创建失败"
                return self.install_result
            
            # 启动服务
            if not self.start_mysql_service():
                self.install_result['status'] = 'failed'
                self.install_result['message'] = f"{self.host} MySQL服务启动失败"
                return self.install_result
            
            # 设置root密码
            if temp_password:
                self.set_root_password(temp_password)
            
            # 配置防火墙
            self.configure_firewall()
            
            # 验证安装
            if self.verify_installation():
                self.install_result['status'] = 'success'
                self.install_result['message'] = f"{self.host} MySQL安装成功"
                logger.info(f"🎉 {self.host} MySQL二进制安装成功！")
                logger.info(f"   🔑 root用户密码: {self.root_password}")
                logger.info(f"   📍 连接命令: {self.install_dir}/bin/mysql -u root -p")
                logger.info(f"   📍 服务管理: systemctl {{start|stop|restart|status}} mysqld")
            else:
                self.install_result['status'] = 'warning'
                self.install_result['message'] = f"{self.host} 安装完成但验证失败"
                logger.warning(f"⚠️  {self.host} 安装完成但验证失败")
                
        except Exception as e:
            error_msg = f"❌ {self.host} 安装过程中发生错误: {str(e)}"
            logger.error(error_msg)
            self.install_result['status'] = 'failed'
            self.install_result['message'] = error_msg
        finally:
            self.cleanup()
            if self.ssh:
                self.ssh.close()
            self.install_result['end_time'] = datetime.now()
        
        return self.install_result

class ConfigManager:
    @staticmethod
    def load_from_json(config_file):
        """从JSON文件加载配置"""
        try:
            with open(config_file, 'r', encoding='utf-8') as f:
                config = json.load(f)
            return config
        except Exception as e:
            logger.error(f"加载JSON配置文件失败: {str(e)}")
            return None
    
    @staticmethod
    def load_from_csv(csv_file):
        """从CSV文件加载配置"""
        hosts = []
        try:
            with open(csv_file, 'r', encoding='utf-8') as f:
                reader = csv.DictReader(f)
                for row in reader:
                    # 处理空值
                    host_info = {}
                    for key, value in row.items():
                        if value and value.lower() not in ['null', 'none', '']:
                            host_info[key] = value
                        else:
                            host_info[key] = None
                    hosts.append(host_info)
            return hosts
        except Exception as e:
            logger.error(f"加载CSV配置文件失败: {str(e)}")
            return []
    
    @staticmethod
    def load_from_dict(config_dict):
        """从字典加载配置"""
        return config_dict

def batch_install(hosts_config, global_config, max_workers=5):
    """批量安装MySQL"""
    logger.info(f"🚀 开始批量安装MySQL，共 {len(hosts_config)} 台主机")
    
    results = []
    failed_hosts = []
    
    with ThreadPoolExecutor(max_workers=max_workers) as executor:
        # 提交所有安装任务
        future_to_host = {
            executor.submit(install_single_mysql, host_info, global_config): host_info['host']
            for host_info in hosts_config
        }
        
        # 收集结果
        for future in as_completed(future_to_host):
            host = future_to_host[future]
            try:
                result = future.result()
                results.append(result)
                
                if result['status'] == 'failed':
                    failed_hosts.append(host)
                    logger.error(f"❌ {host} 安装失败: {result['message']}")
                elif result['status'] == 'success':
                    logger.info(f"✅ {host} 安装成功")
                else:
                    logger.warning(f"⚠️  {host} 安装完成但有警告")
                    
            except Exception as e:
                error_msg = f"❌ {host} 安装任务执行异常: {str(e)}"
                logger.error(error_msg)
                results.append({
                    'host': host,
                    'status': 'failed',
                    'message': error_msg,
                    'start_time': None,
                    'end_time': None
                })
                failed_hosts.append(host)
    
    # 生成安装报告
    generate_report(results, failed_hosts)
    
    return results

def install_single_mysql(host_info, global_config):
    """安装单台主机的MySQL"""
    installer = MySQLBinaryInstaller(host_info, global_config)
    return installer.install()

def generate_report(results, failed_hosts):
    """生成安装报告"""
    total = len(results)
    success = sum(1 for r in results if r['status'] == 'success')
    failed = len(failed_hosts)
    warning = sum(1 for r in results if r['status'] == 'warning')
    
    report = f"""
📊 MySQL批量安装报告
{'='*50}
总主机数: {total}
成功: {success}
失败: {failed}
警告: {warning}
成功率: {success/total*100:.1f}%

失败主机列表:
{chr(10).join([f'  - {host}' for host in failed_hosts]) if failed_hosts else '  无'}

详细信息请查看日志文件: mysql_install.log
"""
    
    logger.info(report)
    
    # 保存报告到文件
    with open('mysql_install_report.txt', 'w', encoding='utf-8') as f:
        f.write(report)

def main():
    parser = argparse.ArgumentParser(description='远程批量安装MySQL二进制版本')
    parser.add_argument('--config', required=True, help='配置文件路径 (JSON/CSV格式)')
    parser.add_argument('--format', choices=['json', 'csv'], default='json', help='配置文件格式')
    parser.add_argument('--workers', type=int, default=5, help='并发安装线程数 (默认: 5)')
    parser.add_argument('--default-password', help='默认SSH密码')
    parser.add_argument('--default-mysql-password', help='默认MySQL root密码')
    
    args = parser.parse_args()
    
    # 加载配置
    if args.format == 'json':
        config = ConfigManager.load_from_json(args.config)
        if not config:
            sys.exit(1)
        
        global_config = config.get('global', {})
        hosts_config = config.get('hosts', [])
        
    else:  # csv
        hosts_config = ConfigManager.load_from_csv(args.config)
        global_config = {}
    
    # 设置默认密码
    if args.default_password:
        global_config['default_password'] = args.default_password
    if args.default_mysql_password:
        global_config['default_root_password'] = args.default_mysql_password
    
    # 如果没有设置密码，交互式输入
    if not global_config.get('default_password'):
        global_config['default_password'] = getpass("请输入默认SSH密码: ")
    
    if not global_config.get('default_root_password'):
        global_config['default_root_password'] = getpass("请输入默认MySQL root密码: ")
    
    # 验证必要配置
    if not hosts_config:
        logger.error("❌ 没有找到主机配置信息")
        sys.exit(1)
    
    # 开始批量安装
    results = batch_install(hosts_config, global_config, args.workers)
    
    # 统计结果
    success_count = sum(1 for r in results if r['status'] == 'success')
    total_count = len(results)
    
    if success_count == total_count:
        logger.info("🎉 所有主机MySQL安装完成！")
        sys.exit(0)
    else:
        logger.error(f"❌ 部分主机安装失败 ({success_count}/{total_count})")
        sys.exit(1)

if __name__ == "__main__":
    main()
